//	Ordinaire.Database.SqlQuery
//	Copyright (c) 2008 Jonathan Loe
//
//	MIT license (http://en.wikipedia.org/wiki/MIT_License)
//
//	Permission is hereby granted, free of charge, to any person obtaining a copy
//	of this software and associated documentation files (the "Software"), to deal
//	in the Software without restriction, including without limitation the rights 
//	to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
//	of the Software, and to permit persons to whom the Software is furnished to do so, 
//	subject to the following conditions:
//
//	The above copyright notice and this permission notice shall be included in all 
//	copies or substantial portions of the Software.
//
//	THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, 
//	INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
//	PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE 
//	FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
//	ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;

namespace Ordinaire.Database
{
	/// <summary>
	/// This class helps generates Sql Statement based on the object given.
	/// </summary>
	public class SqlQuery
	{
		#region Public methods
		#region DELETE...
		/// <summary>
		/// Generates SQL DELETE statement with the specified object value and condition fields.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="conditionFields">field names as condition statement</param>
		/// <returns>SQL statement</returns>
		public static string GenerateDeleteStatement(object value, string[] conditionFields)
		{
			return GenerateDeleteStatement(value, conditionFields, null);
		}

		/// <summary>
		/// Generates SQL DELETE statement with the specified object value, condition fields and table name.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="conditionFields">field names as condition statement</param>
		/// <param name="tableName">table name</param>
		/// <returns>SQL statement</returns>
		public static string GenerateDeleteStatement(object value, string[] conditionFields, string tableName)
		{
			if (value == null) { throw new ArgumentNullException("value"); }
			if (conditionFields == null || conditionFields.Length == 0)
				throw new ArgumentException(String.Format(ExceptionMessage.NullOrEmptyListFormat, "conditionFields"));

			Type type = value.GetType();
			if (String.IsNullOrEmpty(tableName)) { tableName = type.Name; }

			StringBuilder sbCommandText = new StringBuilder();
			sbCommandText.Append(String.Format("DELETE FROM [{0}] ", tableName));

			PropertyInfo[] propertyInfo = type.GetProperties();
			List<PropertyInfo> propertyColumns;
			List<PropertyInfo> conditionColumns;
			SplitIntoPropertyAndConditionColumns(propertyInfo, conditionFields, null, out propertyColumns, out conditionColumns);

			// Throw exception if no condition
			if (conditionColumns == null || conditionColumns.Count == 0)
			{
				throw new ArgumentException(
					String.Format(ExceptionMessage.NoMatchingFieldNameFormat, "conditionFields")
				);
			}

			sbCommandText.Append("WHERE ");

			// Form condition text
			for (int i = 0; i < conditionColumns.Count; i++)
			{
				PropertyInfo info = conditionColumns[i];

				if (i != 0) { sbCommandText.Append(" AND "); }

				// Set the column name
				string columnName = info.Name;

				// Set the column value
				object obj = info.GetValue(value, null);
				string columnValue = ConvertToValueString(obj);

				sbCommandText.Append(String.Format("[{0}] = {1}", columnName, columnValue));
			}

			return sbCommandText.ToString();
		}
		#endregion

		#region INSERT...
		/// <summary>
		/// Generates SQL INSERT statement with the specified object value.
		/// </summary>
		/// <param name="value">object value</param>
		/// <returns>SQL statement</returns>
		public static string GenerateInsertStatement(object value)
		{
			return GenerateInsertStatement(value, null, null);
		}

		/// <summary>
		/// Generates SQL INSERT statement with the specified object value and field names to exclude.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="excludeFields">field names to exclude</param>
		/// <returns>SQL statement</returns>
		public static string GenerateInsertStatement(object value, string[] excludeFields)
		{
			return GenerateInsertStatement(value, excludeFields, null);
		}

		/// <summary>
		/// Generates SQL INSERT statement with the specified object value, field names to exclude and
		/// table name.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="excludeFields">field names to exclude</param>
		/// <param name="tableName">table name</param>
		/// <returns>SQL statement</returns>
		public static string GenerateInsertStatement(object value, string[] excludeFields, string tableName)
		{
			if (value == null) { throw new ArgumentNullException("value"); }

			Type type = value.GetType();
			if (String.IsNullOrEmpty(tableName)) { tableName = type.Name; }

			StringBuilder sbCommandText = new StringBuilder();
			sbCommandText.Append(String.Format(" INSERT [{0}] ", tableName));

			PropertyInfo[] propertyInfo = type.GetProperties();
			List<PropertyInfo> propertyColumns;
			List<PropertyInfo> conditionColumns;
			SplitIntoPropertyAndConditionColumns(propertyInfo, null, excludeFields, out propertyColumns, out conditionColumns);

			// Throw exception if no field to insert
			if (propertyColumns == null || propertyColumns.Count == 0)
			{
				throw new ArgumentException(
					String.Format(ExceptionMessage.NoFieldToFormat, "insert")
				);
			}

			StringBuilder sbColumnNames = new StringBuilder();
			StringBuilder sbColumnValues = new StringBuilder();
			// Form command text of set of properties to insert
			for (int i = 0; i < propertyColumns.Count; i++)
			{
				PropertyInfo info = propertyColumns[i];

				if (i != 0)
				{
					sbColumnNames.Append(", ");
					sbColumnValues.Append(", ");
				}

				// Set the column name
				string columnName = info.Name;
				sbColumnNames.Append(String.Format("[{0}]", columnName));

				// Set the column value
				object obj = info.GetValue(value, null);
				string columnValue = ConvertToValueString(obj);
				sbColumnValues.Append(columnValue);
			}

			// Form command text
			sbCommandText.Append(String.Format("({0}) VALUES ({1})", sbColumnNames.ToString(), sbColumnValues.ToString()));

			return sbCommandText.ToString();
		}
		#endregion

		#region UPDATE...
		/// <summary>
		/// Generates SQL UPDATE statement with the specified object and field names as its condition.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="conditionFields">field names as condition</param>
		/// <returns>SQL statement</returns>
		public static string GenerateUpdateStatement(object value, string[] conditionFields)
		{
			return GenerateUpdateStatement(value, conditionFields, null, null);
		}

		/// <summary>
		/// Generates SQL UPDATE statement with the specified object, field names as its condition,
		/// and field names to exclude.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="conditionFields">field names as condition</param>
		/// <param name="excludeFields">field names to exclude</param>
		/// <returns>SQL statement</returns>
		public static string GenerateUpdateStatement(object value, string[] conditionFields, string[] excludeFields)
		{
			return GenerateUpdateStatement(value, conditionFields, excludeFields, null);
		}

		/// <summary>
		/// Generates SQL UPDATE statement with the specified object, field names as its condition,
		/// field names to exclude, and table name.
		/// </summary>
		/// <param name="value">object value</param>
		/// <param name="conditionFields">field names as condition</param>
		/// <param name="excludeFields">field names to exclude</param>
		/// <param name="tableName">table name</param>
		/// <returns>SQL statement</returns>
		public static string GenerateUpdateStatement(object value, string[] conditionFields, string[] excludeFields, string tableName)
		{
			if (value == null) { throw new ArgumentNullException("value"); }
			if (conditionFields == null || conditionFields.Length == 0)
				throw new ArgumentException(String.Format(ExceptionMessage.NullOrEmptyListFormat, "conditionFields"));

			Type type = value.GetType();
			if (String.IsNullOrEmpty(tableName)) { tableName = type.Name; }

			StringBuilder sbCommandText = new StringBuilder();
			sbCommandText.Append(String.Format(" UPDATE [{0}] SET ", tableName));

			PropertyInfo[] propertyInfo = type.GetProperties();
			List<PropertyInfo> propertyColumns;
			List<PropertyInfo> conditionColumns;
			SplitIntoPropertyAndConditionColumns(propertyInfo, conditionFields, excludeFields, out propertyColumns, out conditionColumns);

			// Throw exception if no field to update
			if (propertyColumns == null || propertyColumns.Count == 0)
			{
				throw new ArgumentException(
					String.Format(ExceptionMessage.NoFieldToFormat, "update")
				);
			}
			if (conditionColumns == null || conditionColumns.Count == 0)
			{
				throw new ArgumentException(
					String.Format(ExceptionMessage.NoMatchingFieldNameFormat, "conditionFields")
				);
			}

			// Form command text of set of properties to update
			for (int i = 0; i < propertyColumns.Count; i++)
			{
				PropertyInfo info = propertyColumns[i];

				// Set the column name
				string columnName = info.Name;

				// Set the column value
				object obj = info.GetValue(value, null);
				string columnValue = ConvertToValueString(obj);

				// Form command text
				if (i != 0) { sbCommandText.Append(", "); }
				sbCommandText.Append(String.Format("[{0}] = {1}", columnName, columnValue));
			}

			sbCommandText.Append(" WHERE ");

			// Form condition text of update conditions (WHERE statement)
			for (int j = 0; j < conditionColumns.Count; j++)
			{
				PropertyInfo info = conditionColumns[j];

				// Set the column name
				string columnName = info.Name;

				// Set the column value
				object obj = info.GetValue(value, null);
				string columnValue = ConvertToValueString(obj);

				if (j != 0) { sbCommandText.Append(" AND "); }
				sbCommandText.Append(String.Format("[{0}] = {1}", columnName, columnValue));
			}

			return sbCommandText.ToString();
		}
		#endregion
		#endregion

		#region Private methods
		/// <summary>
		/// Converts Object class to a string used as part of command text.
		/// </summary>
		/// <param name="value">value object</param>
		/// <returns>converted string</returns>
		private static string ConvertToValueString(object value)
		{
			string text = String.Empty;

			if (value == null) { text = "NULL"; }
			else if (value is string) { text = String.Format("'{0}'", ((string)value).Replace("'", "''")); }
			else if (value is DateTime) { text = String.Format("'{0}'", (DateTime)value); }
			else { text = value.ToString(); }

			return text;
		}

		/// <summary>
		/// Split list of properties into list of update and condition properties.
		/// </summary>
		/// <param name="infos">list of properties</param>
		/// <param name="conditionFields">field names as condition</param>
		/// <param name="excludeFields">field names to exclude</param>
		/// <param name="updateColumns">list of update properties</param>
		/// <param name="conditionColumns">list of condition properties</param>
		private static void SplitIntoPropertyAndConditionColumns
		(
			PropertyInfo[] infos,
			string[] conditionFields,
			string[] excludeFields,
			out List<PropertyInfo> propertyColumns,
			out List<PropertyInfo> conditionColumns
		)
		{
			if (infos == null) { throw new ArgumentNullException("infos"); }
			if (infos.Length == 0) { throw new ArgumentException("infos cannot be empty."); }

			bool isExcluded = false;

			// Initialize column list
			propertyColumns = new List<PropertyInfo>();
			conditionColumns = new List<PropertyInfo>();

			for (int i = 0; i < infos.Length; i++)
			{
				isExcluded = false;
				PropertyInfo info = infos[i];

				// Put properties into the condition list
				for (int j = 0; conditionFields != null && j < conditionFields.Length; j++)
				{
					if (conditionFields[j].Equals(info.Name))
					{
						conditionColumns.Add(info);
						isExcluded = true;
					}
				}

				// Exclude properties indicated in the excluded fields
				for (int k = 0; excludeFields != null && k < excludeFields.Length; k++)
				{
					if (excludeFields[k].Equals(info.Name)) { isExcluded = true; }
				}

				if (!isExcluded) { propertyColumns.Add(info); }
			}
		}
		#endregion
	}
}
